----------------- 1_什么是数据库事务？ ---------------------------

CREATE DATABASE trx;
USE trx;

CREATE TABLE account(
    id INT PRIMARY KEY,
    name VARCHAR(20),
    balance DECIMAL(18, 2)
);

-- 往账户表中插入两条记录
INSERT INTO account(id, name, balance) VALUES(1, '张三', 300);
INSERT INTO account(id, name, balance) VALUES(2, '李四', 400);

-- 张三给李四转账 200 元
mysql -h 192.168.126.131 -u root -p

-- 首先，张三通过下面的 SQL 查询张三的账户的余额为 300
SELECT balance FROM account WHERE id = 1;
+---------+
| balance |
+---------+
|  300.00 |
+---------+

-- 然后，从张三的账户中扣除 200 元，SQL 如下：
UPDATE account SET balance = balance - 200 WHERE id = 1;

-- 最后，从李四的账户中增加 200 元，SQL 如下：
UPDATE account SET balance = balance + 200 WHERE id = 2;

-- 考虑这样一个场景：当执行到第二条 UPDATE 语句的时候，MySQL 服务器崩溃了，会发生什么呢？
-- 这种场景导致张三损失了 200 元，因为这 200 元没有加到李四的账户上。

-- 那么怎么避免这个问题的出现呢？
-- 答案就是将上面三个 SQL 放到同一个事务中

-- 开启一个新事务
BEGIN;
SELECT balance FROM account WHERE id = 1;
UPDATE account SET balance = balance - 200 WHERE id = 1;
-- 下面的语句执行失败
UPDATE account SET balance = balance + 200 WHERE id = 2;
-- 回滚这个新事务
ROLLBACK;

-- 开启一个新事务
BEGIN;
SELECT balance FROM account WHERE id = 1;
UPDATE account SET balance = balance - 200 WHERE id = 1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
-- 提交这个新事务
COMMIT;

---------------- 2_两种开启事务的方法：BEGIN 和 START TRANSACTION -----
START TRANSACTION;
mysql> SELECT balance FROM account WHERE id = 1;
+---------+
| balance |
+---------+
|  100.00 |
+---------+
UPDATE account SET balance = balance - 50 WHERE id = 1;
mysql> SELECT balance FROM account WHERE id = 1;
+---------+
| balance |
+---------+
|   50.00 |
+---------+
-- 回滚
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM account WHERE id = 1;
+---------+
| balance |
+---------+
|  100.00 |
+---------+

-- 创建一张临时表
CREATE TEMPORARY TABLE test (id INT, name VARCHAR(30));

START TRANSACTION READ ONLY;
mysql> SELECT balance FROM account WHERE id = 1;
+---------+
| balance |
+---------+
|  100.00 |
+---------+
-- 不能修改普通表
mysql> UPDATE account SET balance = balance - 50 WHERE id = 1;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

-- 但是可以修改临时表
-- 这是因为临时表只能在当前会话中可见，随便改不会影响到其他的会话中的事务
mysql> INSERT INTO test VALUES(1, 'douma'), (2, 'jeffy');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | douma |
|    2 | jeffy |
+------+-------+

COMMIT;

-- 开启一个新事务
START TRANSACTION READ WRITE;

mysql> SELECT balance FROM account WHERE id = 1;
+---------+
| balance |
+---------+
|  100.00 |
+---------+

UPDATE account SET balance = balance - 50 WHERE id = 1;
UPDATE account SET balance = balance + 50 WHERE id = 2;
-- 提交这个新事务
COMMIT;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |  650.00 |
+----+--------+---------+

----------  3_事务的自动提交和隐式提交 ------------------
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |  650.00 |
+----+--------+---------+

-- 李四取 100 元出来
UPDATE account SET balance = balance - 100 WHERE id = 2;
-- 执行完上面的 SQL 事务自动提交

mysql> SELECT * FROM account WHERE id = 2;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  2 | 李四   |  550.00 |
+----+--------+---------+

-- 这个时候想尝试回滚，但是回滚失败
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account WHERE id = 2;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  2 | 李四   |  550.00 |
+----+--------+---------+

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |  550.00 |
+----+--------+---------+

UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  150.00 |
|  2 | 李四   |  450.00 |
+----+--------+---------+

-- 回滚事务
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- 下面的 SQL 属于一个事务
mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |  550.00 |
+----+--------+---------+

UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  150.00 |
|  2 | 李四   |  450.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

-- 下面的 SQL 属于下一个事务了
mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  150.00 |
|  2 | 李四   |  450.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

-- 显式的启动一个事务
BEGIN;
mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  150.00 |
|  2 | 李四   |  450.00 |
+----+--------+---------+
UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  250.00 |
|  2 | 李四   |  350.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

COMMIT;

-- 显式的启动一个事务
BEGIN;
mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  250.00 |
|  2 | 李四   |  350.00 |
+----+--------+---------+

-- 李四转账 100 元给张三
UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  350.00 |
|  2 | 李四   |  250.00 |
+----+--------+---------+

-- 此语句会隐式提交前面语句所属的事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- 回滚前面事务失败，说明前面的事务已经提交了
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  350.00 |
|  2 | 李四   |  250.00 |
+----+--------+---------+

-- 显式的启动一个事务
BEGIN;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  350.00 |
|  2 | 李四   |  250.00 |
+----+--------+---------+

-- 李四转账 100 元给张三
UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  450.00 |
|  2 | 李四   |  150.00 |
+----+--------+---------+

-- 创建一张表
-- 此语句会隐式提交前面语句所属的事务
CREATE TABLE tt(id INT);

-- 回滚前面事务失败，说明前面的事务已经提交了
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  450.00 |
|  2 | 李四   |  150.00 |
+----+--------+---------+

------------------ 4_Save Point、事务 ID、支持事务的存储引擎 ------------------

BEGIN;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  450.00 |
|  2 | 李四   |  150.00 |
+----+--------+---------+

-- 李四向张三转账 10 元
UPDATE account SET balance = balance - 10 WHERE id = 2;
-- 设置一个保存点
mysql> SAVEPOINT s1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  450.00 |
|  2 | 李四   |  140.00 |
+----+--------+---------+
-- 更新语句中参数写错了 (应该是加 10 元，但是只加了 1 元)
UPDATE account SET balance = balance + 1 WHERE id = 1;

-- 回滚到保存点 s1，而不是全部回滚
mysql> ROLLBACK TO s1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  450.00 |
|  2 | 李四   |  140.00 |
+----+--------+---------+

UPDATE account SET balance = balance + 10 WHERE id = 1;

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  460.00 |
|  2 | 李四   |  140.00 |
+----+--------+---------+

USE trx;
CREATE TABLE t1(id INT) ENGINE=MyISAM;

BEGIN;

mysql> INSERT INTO t1 VALUE(1);
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
+------+

-- 在会话 A 中
BEGIN;

-- 执行完 BEGIN 后，事务不会立马启动
-- 如下查看这个事务的信息
mysql> SELECT * FROM information_schema.INNODB_TRX\G;

-- 只有在这个事务中执行第一条 SQL 语句的时候，才会启动一个事务
UPDATE account SET balance = balance - 100 WHERE id = 2;


-- 如下查看这个事务的信息
mysql> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 3529010
                 trx_state: RUNNING
               trx_started: 2022-09-23 16:17:44
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 12
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

-- 回滚事务
ROLLBACK;

--------------------- 5_事务的四大特性：ACID ---------------------
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

-- 设置隔离级别为
SET transaction_isolation = 'READ-COMMITTED';

SET transaction_isolation = 'READ-COMMITTED';

USE trx;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  460.00 |
|  2 | 李四   |  140.00 |
+----+--------+---------+

BEGIN
-- 张三转账 100 元给李四
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

ROLLBACK;
-- 在前面的例子中，一致性确保了，即使执行第一条 UPDATE 语句和第二条 UPDATE 语句之间时系统崩溃，张三账户中也不会损失 200 元，
-- 因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库，可以回滚回到原来的状态
mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  460.00 |
|  2 | 李四   |  140.00 |
+----+--------+---------+

BEGIN;
-- 张三转账 100 元给李四
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

mysql> SELECT * FROM account;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | 张三   |  360.00 |
|  2 | 李四   |  240.00 |
+----+--------+---------+

------------------ undo log -----------------------
USE trx;
-- 创建一张表
CREATE TABLE book(
	id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    author varchar(50) NOT NULL,
    INDEX name_idx(name)
);

-- 在 InnoDB 中，每张表都有一个唯一的 id
-- 表 book 的 table_id 是 1134
mysql> SELECT * FROM information_schema.innodb_tables WHERE name = 'trx/book'\G;
*************************** 1. row ***************************
          TABLE_ID: 1134
              NAME: trx/book
              FLAG: 33
            N_COLS: 6
             SPACE: 72
        ROW_FORMAT: Dynamic
     ZIP_PAGE_SIZE: 0
        SPACE_TYPE: Single
      INSTANT_COLS: 0
TOTAL_ROW_VERSIONS: 0

-- 显示开启一个事务，假设这个事务的 id 是 1000
-- 第一个事务
BEGIN;
-- 插入两条记录
INSERT INTO book(name, author)
	VALUES('天龙八部(钟汉良)', '金庸'), ('绝代双骄', '古龙');

-- 删除主键为 1 的记录
DELETE FROM book WHERE id = 1;

-- 更新一条记录
UPDATE book SET name = '射雕英雄传', author = '金庸' WHERE id = 2;

-- 更新主键
UPDATE book SET id = 1000 WHERE id = 2;

-- 查询 undo 数据存放的文件目录
-- undo log 默认存储在 MySQL 的数据目录中
mysql> select @@innodb_undo_directory;
+-------------------------+
| @@innodb_undo_directory |
+-------------------------+
| ./                      |
+-------------------------+
1 row in set (0.00 sec)

-- 在磁盘中 undo log 也是按照表空间的方式来组织的
-- 查询 undo 表空间的个数
-- 2 个表空间分别对应着文件：undo_001 和 undo_002
mysql> select @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.01 sec)

-- 每个 undo 表空间中由多个回滚段组成
-- 查询回滚段的个数
mysql> select @@innodb_rollback_segments;
+----------------------------+
| @@innodb_rollback_segments |
+----------------------------+
|                        128 |
+----------------------------+
1 row in set (0.00 sec)